﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using PowerCreator.LMS.DL.Project.Entity;
using PowerCreator.LMS.Web.DL.Project;
using PowerCreator.LMS.Common.Script;

public partial class sysadmin_DL_Project_ManageSchoolInfo : PowerCreator.LMS.Web.Base.WebBasePage
{

    TOClass objClass = new TOClass();
    PowerCreator.LMS.ViewQuery.ViewQuery obj = new PowerCreator.LMS.ViewQuery.ViewQuery();
    private PowerCreator.LMS.Web.DL.User.LoginEntity login = new PowerCreator.LMS.Web.DL.User.LoginEntity();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            InitPage();
            BinderPGA();
        }
    }

    protected override void InitPage()
    {
        base.InitPage();
    }
    private string GetSearch()
    {
        string search = "select * from TO_SchoolGroup where SchoolGroupParentId =0";
        if (!string.IsNullOrEmpty(txt_KeyWord.Text))
            search += string.Format(" and SchoolGroupName like '%{0}%'", txt_KeyWord.Text.Trim());
        //search += System.String.Format(" and TO_Class.Class_Id in (Select FkOID from TauthorityUser as a , TauthorityList as b where a. FkAuthorityID = b.FkAuthorityID and FkUserID ={0} and b.authorityType=2)", GetLoginEntity.UserID);
        return search;
    }

    private void BinderPGA()
    {
        string searchView = "";
        int intScoresCol = 0;
        int intScores = 0;
        int intHours = 0;
        int intWorkNum = 0;
        int intWorReply = 0;
        int intExam = 0;
        string searchStu = "select * from to_learner where School={0}";
        string search = "select * from TO_SchoolGroup where SchoolGroupParentId =0";
        DataTable dtSchool = obj.Query(search);
        dtSchool.Columns.Add("avgScore",typeof(int));
        dtSchool.Columns.Add("levelSchool");
        if(dtSchool.Rows.Count>0)
        {
            for (int i = 0; i < dtSchool.Rows.Count; i++)
            {
                searchStu = string.Format(searchStu, dtSchool.Rows[i]["SchoolGroup_Id"].ToString());
                DataTable dtStu = obj.Query(searchStu);
                if (dtStu.Rows.Count > 0)
                {
                    for (int j = 0; j < dtStu.Rows.Count;j++ )
                    {
                        //得到每个学员的总学时 
                        string searchCourse = "select sum(SscrlogTotalTime) as studyHours,FKSscrlogUserID from TStudentStudyCouReslog group by FKSscrlogUserID having FKSscrlogUserID={0}";
                        searchView = string.Format(searchCourse, dtStu.Rows[j]["Learner_Id"].ToString());
                        DataTable dtCourse = obj.Query(searchView);
                        if(dtCourse.Rows.Count>0)
                        {
                            if (string.IsNullOrEmpty(dtCourse.Rows[0]["studyHours"].ToString()))
                            {
                                intHours = 0;
                            }
                            else 
                            {
                                intHours = int.Parse(dtCourse.Rows[0]["studyHours"].ToString());
                            }
                            
                        }
                        //得到作业完成总次数
                        string searchWorkNum = "select count(Creator) as WorkNum from TO_ClassTasks where Creator ={0}";
                        searchView = string.Format(searchWorkNum, dtStu.Rows[j]["Learner_Id"].ToString());
                        DataTable dtWorkNum = obj.Query(searchView);
                        if(dtWorkNum.Rows.Count>0)
                        {
                            if (string.IsNullOrEmpty(dtWorkNum.Rows[0]["WorkNum"].ToString()))
                            {
                                intWorkNum = 0;
                            }
                            else
                            {
                                intWorkNum = int.Parse(dtWorkNum.Rows[0]["WorkNum"].ToString());
                            }
                            
                        }
                        //得到作业评论总次数
                        string searchTasksReply = "select count(*) as ReplyNum ,Creator from (select  b.Creator from TO_ClassTasksReply as a ,TO_UserClassTask as b ,TO_ClassTasks as c  where a.UserTask_Id = b.UserTask_Id and b.task_Id = c.ClassTasks_Id ) as tb group by Creator  having Creator ={0} ";
                        searchView = string.Format(searchTasksReply, dtStu.Rows[j]["Learner_Id"].ToString());
                        DataTable dtTasksReply = obj.Query(searchView);
                        if (dtTasksReply.Rows.Count > 0)
                        {
                            if (string.IsNullOrEmpty(dtTasksReply.Rows[0]["ReplyNum"].ToString()))
                            {
                                intWorReply = 0;
                            }
                            else
                            {
                                intWorReply = int.Parse(dtTasksReply.Rows[0]["ReplyNum"].ToString());
                            }
                            
                        }
                        //得到考试总成绩
                        string searchExam = "select sum(UPHistoryScore) as UPHistoryScore from UserPaperHistory where fKUPHistoryUserID ={0}";
                        searchView = string.Format(searchExam,dtStu.Rows[j]["Learner_Id"].ToString());
                        DataTable dtExam = obj.Query(searchView);
                        if(dtExam.Rows.Count>0)
                        {
                            if (string.IsNullOrEmpty(dtExam.Rows[0]["UPHistoryScore"].ToString()))
                            {
                                intExam = 0;
                            }
                            else
                            {
                                intExam = int.Parse(dtExam.Rows[0]["UPHistoryScore"].ToString());
                            }
                            
                        }
                        intScores=0;
                        intScores = intWorkNum + intWorReply + intExam;//得到每个学员的
                    }
                    intScoresCol = intScoresCol + intScores;
                }
                if (dtStu.Rows.Count > 0)
                {
                    dtSchool.Rows[i]["avgScore"] = intScoresCol / dtStu.Rows.Count;
                }
                else
                {
                    dtSchool.Rows[i]["avgScore"] = 0;
                }
            }

        }
        //学校排名
        DataView dv = dtSchool.DefaultView;
        if (dtSchool.Rows.Count > 0)
        {
            dv.Sort = "avgScore desc";
            dtSchool = dv.ToTable();
            for (int j = 0; j < dtSchool.Rows.Count; j++)
            {
                dtSchool.Rows[j]["levelSchool"] = j + 1;
            }
        }
        Session["SchoolRank"] = dtSchool;
        rptNew.DataSource = dtSchool;
        rptNew.DataBind();
    }

    private void BinderPGB(string search)
    {
        DataTable dt = obj.Query(search);
        DataTable dtSchool = new DataTable();
        dt.Columns.Add("avgScore");
        dt.Columns.Add("levelSchool");
        if (Session["SchoolRank"] != null)
        {
            dtSchool = (DataTable)Session["SchoolRank"];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dtSchool.Rows.Count; j++)
                {
                    if (dt.Rows[i]["SchoolGroup_Id"].ToString().Equals(dtSchool.Rows[j]["SchoolGroup_Id"].ToString()))
                    {
                        dt.Rows[i]["avgScore"] = dtSchool.Rows[j]["avgScore"].ToString();
                        dt.Rows[i]["levelSchool"] = dtSchool.Rows[j]["levelSchool"].ToString();
                    }
                }
            }
        }

        rptNew.DataSource = dt;
        rptNew.DataBind();
    }

    protected void btnSearch_Click(object sender, EventArgs e)
    {
        string search = GetSearch();
        BinderPGB(search);
    }
}
